Minimizing SQL Server log file size

Depending on the configuration of SQL Server, the SQL Server database log file (.LDF file, not to be confused with the Meridian write-ahead .LOG files) of a Meridian vault can become very large (up to 100 GB). The reason for this is that the default recovery method of SQL Server is Full. If your organization allows the use of the Simple recovery mode, you can truncate the SQL Server log file to optimize performance.

Warning    Do not change the properties of a SQL Server database that is used by Meridian. Especially do not clear the Unrestricted file growth options for Maximum file size for either the data files or the transaction log because when the maximum size of the log file is reached, SQL Server cannot write to the database any more, as it is full.

To minimize the SQL Server log file size:

1. Perform a Prepare for Backup operation for every SQL Server vault as described in Preparing for backups.
2. Open the SQL Query Analyzer included with SQL Server and connect to the SQL Server integrated with Meridian.
3. Paste this script into the editor and replace <DatabaseName> with the name of the database used by the vault:

BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY
DBCC SHRINKDATABASE (<DatabaseName>, TRUNCATEONLY)

This compresses the .LDF file. You can now change the recovery mode to prevent the log from growing again in the future.

Query the current recovery mode by pasting this script into the editor and replacing <DatabaseName> with the same database name you used above:

SELECT DATABASEPROPERTY('<DatabaseName>', 'ISTRUNCLOG')
SELECT DATABASEPROPERTYEX('<DatabaseName>', 'RECOVERY')

4. If it reports that it is in Full recovery mode, change the recovery mode to Simple by pasting this script into the editor and replacing <DatabaseName> with the same database name you used above:

ALTER DATABASE <DatabaseName> SET RECOVERY SIMPLE

If the log file continues to grow after you’ve implemented these changes, consult a SQL Server database administrator to determine whether other SQL Server settings need to be modified to correct this behavior.